# -*- coding: utf-8 -*-

'''比较三种方式的插入效率

    方式1: 纯粹django
    方式2a: django中直接使用sql,使用占位符号
    方法2b: 直接使用填完后的字符串
    方式3: 纯粹sql
    都插入100000条记录

    测试结果:
    1. 82.5789999962    81.625
    2. 27.0780000687    27.2649998665
    3. 29.7339999676    29.6880002022
    4. 6.59400010109    6.31200003624

    500000:
    1. 408.516
    2. 146.14
    3. 154.703
    4. 32.407

    在另一台机器上
    1. 39.3280000687    39.6719999313
    2. 12.375           12.8129999638
    3. 11.3439998627    11.4690001011
    4. 4.125            4.1400001049

    基本结论:纯django和纯sql的性能相差一个数量级,混合型的在中间
             决定对于大批量导入数据,采用纯sql的方式
             日常导入数据，采用django 风格的混合型方式

    方法3的数据库
CREATE TABLE "benchmark_normal3" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(16) NOT NULL,
    "i1" integer NOT NULL,
    "i2" integer NOT NULL,
    "i3" integer NOT NULL,
    "i4" integer NOT NULL,
    "i5" integer NOT NULL,
    "i6" integer NOT NULL
)
    
'''

from django.core.management import setup_environ
import wolfox.foxit.other_settings.settings_benchmark as target
setup_environ(target)
from django.db import transaction

from wolfox.foxit.benchmark.models import Normal,Normal2

TIMES =100000
STEP = 1000

@transaction.commit_manually
def pure_django():
    for i in xrange(TIMES):
        cur = Normal(name='test',i1=i,i2=i,i3=i,i4=i,i5=i,i6=i)
        cur.save()
        if(i%STEP == 0):
            print 'save %d' % i
    transaction.commit()

@transaction.commit_manually
def django_sql(connection):
    cursor = connection.cursor()
    for i in xrange(TIMES):
        cursor.execute('insert into benchmark_normal2(name,i1,i2,i3,i4,i5,i6) values(%s,%s,%s,%s,%s,%s,%s)',(u'test',i,i,i,i,i,i))
        if(i%STEP == 0):
            print 'save %d' % i
    transaction.commit()

@transaction.commit_manually
def django_sql2(connection):
    cursor = connection.cursor()
    for i in xrange(TIMES):
        cursor.execute("insert into benchmark_normal2(name,i1,i2,i3,i4,i5,i6) values('%s',%s,%s,%s,%s,%s,%s)" % (u'test',i,i,i,i,i,i))
        if(i%STEP == 0):
            print 'save %d' % i
    transaction.commit()


def pure_sql(connection):
    cursor = connection.cursor()
    for i in xrange(TIMES):
        cursor.execute('insert into benchmark_normal3(name,i1,i2,i3,i4,i5,i6) values(?,?,?,?,?,?,?)',(u'test',i,i,i,i,i,i))
        if(i%STEP == 0):
            print 'save %d' % i
    connection.commit()

if __name__ == '__main__':
    from time import time
    f = time()
    pure_django()
    t = time()
    print t-f
    from django.db import connection as conn_django
 
    f = time()
    django_sql(conn_django)
    t = time()
    print t-f

    f = time()
    django_sql2(conn_django)
    t = time()
    print t-f


    import sqlite3
    import os.path
    par_path = os.path.join(os.path.dirname(__file__),os.pardir)
    #print par_path
    conn_sql = sqlite3.connect(os.path.join(par_path,'benchmark\\benchmark.db'))
    f = time()
    pure_sql(conn_sql)
    t = time()
    print t-f
